﻿Imports System.Windows.Forms

Module modGeneral
    Public Const TitMsj = "Mensaje VaR Tools"

    Public Sub Exportar_Excel(ByVal dgGrilla As DataGridView)

        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To dgGrilla.ColumnCount - 1
            dset.Tables(0).Columns.Add(dgGrilla.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To dgGrilla.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To dgGrilla.Columns.Count - 1
                dr1(j) = dgGrilla.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        '===============================================
        ' Exportando a Excel el contenido de la Grilla
        '===============================================
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

        xlApp = CType(CreateObject("Excel.Application"),  _
                Microsoft.Office.Interop.Excel.Application)
        xlBook = CType(xlApp.Workbooks.Add,  _
                    Microsoft.Office.Interop.Excel.Workbook)
        xlSheet = CType(xlBook.Worksheets(1),  _
                    Microsoft.Office.Interop.Excel.Worksheet)

        xlSheet.Range("A1:AZ1").Font.Bold = True

        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            xlSheet.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                If dc.ColumnName = "Column1" Then
                    xlSheet.Cells(rowIndex + 1, colIndex) = IIf(dr(dc.ColumnName).ToString.Trim = "", "", " X ")
                Else
                    xlSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                End If
            Next
        Next

        xlSheet.Columns.AutoFit()
        Dim strFileName As String = "C:\Reporte.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        xlApp.Visible = True

    End Sub
End Module
